package com.example.coolweather.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.coolweather.info.CityInfo;
import com.example.coolweather.info.CountyInfo;
import com.example.coolweather.info.ProvinceInfo;
import com.example.coolweather.util.ToastUtil;

import java.util.ArrayList;
import java.util.List;




public class SiteDBHelper extends SQLiteOpenHelper {
    private static final String DB_NAME = "shopping.db";
    private static final String TABLE_PROVINCE_INFO = "province_info"; // 省
    private static final String TABLE_CITY_INFO = "city_info"; // 市
    private static final String TABLE_COUNTY_INFO = "county_info"; // 县
    private static final int DB_VERSION = 1;
    private static SiteDBHelper mHelper = null;
    private SQLiteDatabase mRDB = null;
    private SQLiteDatabase mWDB = null;
    private static final String TAG = "lch-SiteDBHelper";

    private SiteDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    // 利用单例模式获取数据库帮助器的唯一实例
    public static SiteDBHelper getInstance(Context context) {
        if (mHelper == null) {
            mHelper = new SiteDBHelper(context);
        }
        return mHelper;
    }

    // 打开数据库的读连接
    public SQLiteDatabase openReadLink() {
        if (mRDB == null || !mRDB.isOpen()) {
            mRDB = mHelper.getReadableDatabase();
        }
        return mRDB;
    }

    // 打开数据库的写连接
    public SQLiteDatabase openWriteLink() {
        if (mWDB == null || !mWDB.isOpen()) {
            mWDB = mHelper.getWritableDatabase();
        }
        return mWDB;
    }

    // 关闭数据库连接
    public void closeLink() {
        if (mRDB != null && mRDB.isOpen()) {
            mRDB.close();
            mRDB = null;
        }

        if (mWDB != null && mWDB.isOpen()) {
            mWDB.close();
            mWDB = null;
        }
    }

    // 创建数据库，执行建表语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 省
        String province_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_PROVINCE_INFO +
                "(_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                " provinceName VARCHAR NOT NULL," +
                " provinceCode INTEGER NOT NULL);";
        db.execSQL(province_sql);

        // 市
        String city_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_CITY_INFO +
                "(_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                " provinceId INTEGER NOT NULL," +
                " cityName VARCHAR NOT NULL," +
                " cityCode INTEGER NOT NULL);";
        db.execSQL(city_sql);

        // 县
        String county_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_COUNTY_INFO +
                "(_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                " cityId INTEGER NOT NULL," +
                " countyName VARCHAR NOT NULL," +
                " weatherId INTEGER NOT NULL);";
        db.execSQL(county_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    // 添加单个
    ////////////////////////////////////////////////////////////////////////////////////////////////
    // 添加省
    public boolean insertProvinceInfo(ProvinceInfo provinceInfo) {
        Cursor cursor = mRDB.query(TABLE_PROVINCE_INFO, null, "id=?", new String[]{String.valueOf(provinceInfo.id)},
                null, null, null);
        // 如果不存在该省，添加一条信息
        if (!cursor.moveToNext()) {
            ContentValues values = new ContentValues();
            values.put("provinceId", provinceInfo.id);
            // ContentValues Key只能是String类型，values只能存储基本类型的数据，非常适合跟数据库连用
            values.put("provinceName", provinceInfo.provinceName);
            values.put("provinceCode", provinceInfo.provinceCode);
            mWDB.insert(TABLE_PROVINCE_INFO, null, values);
            return true;
        } else {
            Log.d(TAG, "add province 该省已存在");
            return false;
        }
    }
    // 添加市
    public boolean insertCityInfo(CityInfo cityInfo) {
        Cursor cursor = mRDB.query(TABLE_CITY_INFO, null, "id=?", new String[]{String.valueOf(cityInfo.id)},
                null, null, null);
        // 如果不存在该城市，添加一条信息
        if (!cursor.moveToNext()) {
            // ContentValues Key只能是String类型，values只能存储基本类型的数据，非常适合跟数据库连用
            ContentValues values = new ContentValues();
            values.put("cityName", cityInfo.cityName);
            values.put("cityCode", cityInfo.cityCode);
            values.put("provinceId", cityInfo.provinceId);
            mWDB.insert(TABLE_CITY_INFO, null, values);
            return true;
        } else {
            Log.d(TAG, "add city 该城市已存在");
            return false;
        }
    }
    // 添加县
    public boolean insertCountyInfo(CountyInfo countyInfo) {
        Cursor cursor = mRDB.query(TABLE_COUNTY_INFO, null, "id=?", new String[]{String.valueOf(countyInfo.id)},
                null, null, null);
        // 如果购不存在该城市，添加一条信息
        if (!cursor.moveToNext()) {
            // ContentValues Key只能是String类型，values只能存储基本类型的数据，非常适合跟数据库连用
            ContentValues values = new ContentValues();
            values.put("countyName", countyInfo.countyName);
            values.put("weatherId", countyInfo.weatherId);
            values.put("cityId", countyInfo.cityId);
            mWDB.insert(TABLE_COUNTY_INFO, null, values);
            return true;
        } else {
            Log.d(TAG, "add county 该县已存在");
            return false;
        }
    }

    // 插入多个
    ////////////////////////////////////////////////////////////////////////////////////////////////
    // 添加省
    public boolean insertProvinceInfos(List<ProvinceInfo> list) {
        // 插入多条记录，要么全部成功，要么全部失败
        try {
            mWDB.beginTransaction();
            for (ProvinceInfo info : list) {
                // ContentValues Key只能是String类型，values只能存储基本类型的数据，非常适合跟数据库连用
                ContentValues values = new ContentValues();
                values.put("provinceName", info.provinceName);
                values.put("provinceCode", info.provinceCode);
                mWDB.insert(TABLE_PROVINCE_INFO, null, values);
            }
            mWDB.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            mWDB.endTransaction();
        }
        return false;
    }
    // 添加市
    public boolean insertCityInfos(List<CityInfo> list) {
        // 插入多条记录，要么全部成功，要么全部失败
        try {
            mWDB.beginTransaction();
            for (CityInfo info : list) {
                // ContentValues Key只能是String类型，values只能存储基本类型的数据，非常适合跟数据库连用
                ContentValues values = new ContentValues();
                values.put("cityName", info.cityName);
                values.put("cityCode", info.cityCode);
                values.put("provinceId", info.provinceId);
                mWDB.insert(TABLE_CITY_INFO, null, values);
            }
            mWDB.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            mWDB.endTransaction();
        }
        return false;
    }
    // 添加县
    public boolean insertCountyInfos(List<CountyInfo> list) {
        // 插入多条记录，要么全部成功，要么全部失败
        try {
            mWDB.beginTransaction();
            for (CountyInfo info : list) {
                // ContentValues Key只能是String类型，values只能存储基本类型的数据，非常适合跟数据库连用
                ContentValues values = new ContentValues();
                values.put("countyName", info.countyName);
                values.put("weatherId", info.weatherId);
                values.put("cityId", info.cityId);
                mWDB.insert(TABLE_COUNTY_INFO, null, values);
            }
            mWDB.setTransactionSuccessful();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            mWDB.endTransaction();
        }
        return false;
    }

    // 查询
    ////////////////////////////////////////////////////////////////////////////////////////////////
    // 查询所有省
    public List<ProvinceInfo> queryAllProvinceInfo() {
        String sql = "select * from " + TABLE_PROVINCE_INFO;
        List<ProvinceInfo> list = new ArrayList<>();
        // rawQuery()方法用于执行select语句
        Cursor cursor = mRDB.rawQuery(sql, null);
        /*  Cursor是结果集游标，用于对结果集进行随机访问
         * 使用moveToNext()方法可以将游标从当前行移动到下一行
         * 如果已经移过了结果集的最后一行，返回结果为false，否则为true
         * */
        while (cursor.moveToNext()) {
            ProvinceInfo info = new ProvinceInfo();
            info.id = cursor.getInt(0);
            info.provinceName = cursor.getString(1);
            info.provinceCode = cursor.getInt(2);
            list.add(info);
        }
        cursor.close();
        return list;
    }
    // 查询所有市
    public List<CityInfo> queryAllCityInfo() {
        String sql = "select * from " + TABLE_CITY_INFO;
        List<CityInfo> list = new ArrayList<>();
        // rawQuery()方法用于执行select语句
        Cursor cursor = mRDB.rawQuery(sql, null);
        /*  Cursor是结果集游标，用于对结果集进行随机访问
         * 使用moveToNext()方法可以将游标从当前行移动到下一行
         * 如果已经移过了结果集的最后一行，返回结果为false，否则为true
         * */
        while (cursor.moveToNext()) {
            CityInfo info = new CityInfo();
            info.id = cursor.getInt(0);
            info.provinceId = cursor.getInt(1);
            info.cityName = cursor.getString(2);
            info.cityCode = cursor.getInt(3);
            list.add(info);
        }
        cursor.close();
        return list;
    }
    // 查询所有县
    public List<CountyInfo> queryAllCountyInfo() {
        String sql = "select * from " + TABLE_COUNTY_INFO;
        List<CountyInfo> list = new ArrayList<>();
        // rawQuery()方法用于执行select语句
        Cursor cursor = mRDB.rawQuery(sql, null);
        /*  Cursor是结果集游标，用于对结果集进行随机访问
         * 使用moveToNext()方法可以将游标从当前行移动到下一行
         * 如果已经移过了结果集的最后一行，返回结果为false，否则为true
         * */
        while (cursor.moveToNext()) {
            CountyInfo info = new CountyInfo();
            info.id = cursor.getInt(0);
            info.cityId = cursor.getInt(1);
            info.countyName = cursor.getString(2);
            info.weatherId = cursor.getString(3);

            list.add(info);
        }
        cursor.close();
        return list;
    }

    // 获取
    ////////////////////////////////////////////////////////////////////////////////////////////////
    // 获取省信息
    public ProvinceInfo getAProvinceInfo(int id) {
        ProvinceInfo info = null;
        Cursor cursor = mRDB.query(TABLE_PROVINCE_INFO, null, "id=?", new String[]{String.valueOf(id)},
                null, null, null);
        if (cursor.moveToNext()) {
            info = new ProvinceInfo();
            info.provinceName = cursor.getString(1);
            info.provinceCode = cursor.getInt(2);
        }
        return info;
    }
    // 获取市信息
    public CityInfo getACityInfo(int id) {
        CityInfo info = null;
        Cursor cursor = mRDB.query(TABLE_CITY_INFO, null, "id=?", new String[]{String.valueOf(id)},
                null, null, null);
        if (cursor.moveToNext()) {
            info = new CityInfo();
            info.id = cursor.getInt(0);
            info.provinceId = cursor.getInt(1);
            info.cityName = cursor.getString(2);
            info.cityCode = cursor.getInt(3);
        }
        return info;
    }
    // 获取县信息
    public CountyInfo getACountyInfo(int id) {
        CountyInfo info = null;
        Cursor cursor = mRDB.query(TABLE_COUNTY_INFO, null, "id=?", new String[]{String.valueOf(id)},
                null, null, null);
        if (cursor.moveToNext()) {
            info = new CountyInfo();
            info.id = cursor.getInt(0);
            info.cityId = cursor.getInt(1);
            info.countyName = cursor.getString(2);
            info.weatherId = cursor.getString(3);
        }
        return info;
    }

    // 查询
    ////////////////////////////////////////////////////////////////////////////////////////////////
    // 查询省下的所有市信息
    public List<CityInfo> queryCityInfoByProvinceId(int provinceId) {
        List<CityInfo> list = new ArrayList<>();
        Cursor cursor = mRDB.query(TABLE_CITY_INFO, null, "provinceId=?", new String[]{String.valueOf(provinceId)},
                null, null, null);
        while (cursor.moveToNext()) {
            CityInfo info = new CityInfo();
            info.id = cursor.getInt(0);
            info.provinceId = cursor.getInt(1);
            info.cityName = cursor.getString(2);
            info.cityCode = cursor.getInt(3);
            list.add(info);
        }
        return list;
    }
    // 查询市下的所以县信息
    public List<CountyInfo> queryCountyInfoByCityId(int cityId) {
        List<CountyInfo> list = new ArrayList<>();
        Cursor cursor = mRDB.query(TABLE_COUNTY_INFO, null, "cityId=?", new String[]{String.valueOf(cityId)},
                null, null, null);
        while (cursor.moveToNext()) {
            CountyInfo info = new CountyInfo();
            info.id = cursor.getInt(0);
            info.cityId = cursor.getInt(1);
            info.countyName = cursor.getString(2);
            info.weatherId = cursor.getString(3);
            list.add(info);
        }
        return list;
    }

    // 删除
    ////////////////////////////////////////////////////////////////////////////////////////////////
    // 删除省
    public void deleteProvince(int id) {
        deleteCityInfoByProvinceId(id);
        mWDB.delete(TABLE_PROVINCE_INFO, "id=?", new String[]{String.valueOf(id)});

    }
    // 根据省ID删除市，并删除所有县
    public void deleteCityInfoByProvinceId(int provinceId) {
        List<CityInfo> list = queryCityInfoByProvinceId(provinceId); // 获取省下的市
        for (CityInfo info : list) {
            deleteCountyInfoByCityId(info.id);
        }
        mWDB.delete(TABLE_CITY_INFO, "provinceId=?", new String[]{String.valueOf(provinceId)});
    }
    // 根据市ID删除县
    public void deleteCountyInfoByCityId(int cityId) {
        mWDB.delete(TABLE_COUNTY_INFO, "cityId=?", new String[]{String.valueOf(cityId)});
    }
    // 删除所有
    public void deleteAllInfo() {
        mWDB.delete(TABLE_COUNTY_INFO, "1=1", null);
        mWDB.delete(TABLE_CITY_INFO, "1=1", null);
        mWDB.delete(TABLE_PROVINCE_INFO, "1=1", null);
    }
}
